package com.szholly.pro.watergas_server.water.localcheck;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;

@Service
public class localCheckService {

	/**
	 * 质控现场考核主表查询
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception{
		
		
		String sql="select t.*,t2.companyname,t1.name,t3.s_domaincaption as monitortype,t4.s_domaincaption as checktype,t5.s_domaincaption from qc_localecheck t" 
				  +" left join BC_SITE_INFO t1 on t1.code=t.stationid"
				  +" left join qc_companyinfo t2 on t2.COMPANYCODE=t.companyid"
				  +" left join data_defulatdomain t3 on t3.s_domaincode=t.monitortype and t3.s_domainname='质控监测类型'"
				  +" left join data_defulatdomain t4 on t4.s_domaincode=t.check_type and t4.s_domainname='质控现场考核'"
				  +" left join data_defulatdomain t5 on t5.s_domaincode=t.check_content and t5.s_domainname='质控现场考核' where 1=1";
		if (params.containsKey("start")) {//开始时间
			sql += " and t.checkdate >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("end")) {//开始时间
			sql += " and t.checkdate <= to_date('" + params.get("end")
					+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("checktype")){
			sql+=" and t.check_type='"+params.get("checktype")+"'";
		}
		if(params.containsKey("STATIONID")){
			sql+=" and t.stationid='"+params.get("STATIONID")+"'";
		}
		if(params.containsKey("GUID")){
			sql+=" and t.GUID='"+params.get("GUID")+"'";
		}
		sql+=" order by t2.companyname,t1.name,t3.s_domaincaption,t4.s_domaincaption,t.checkdate";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 考核详情查询
	 * @param params
	 * @return
	 */
	@RequestMapping(value="getDetail")
	public List<RowBase> getDetail(Map<String, String> params){
		String sql="select t.s_domaincaption as contentname,t1.s_unit,(case t1.s_domaincaption"
				  +" when  cast('常规'as  nvarchar2(10))  then cast('0'as nvarchar2(10))"
				  +" when  cast('文本'as  nvarchar2(10))  then cast('1'as nvarchar2(10))"
				  +" else  t1.s_domaincaption end) as fatherName,t4.* from qc_locale_check_content t4"
 			+" left join data_defulatdomain t on t4.id=t.s_domaincode and  t.s_domainname = '质控现场考核'"
 			+" left join data_defulatdomain t1 on t1.s_domainname = '质控现场考核' and t.s_pid = t1.s_id where 1=1 ";
		if(StringUtil.IsNotNullOrEmpty(params.get("CHECK_CONTENT"))){
			sql+=" and t.s_domaincode like '"+params.get("CHECK_CONTENT")+"%'";
		}
		if(StringUtil.IsNotNullOrEmpty(params.get("GUID"))){
			sql+=" and t4.father_id='"+params.get("GUID")+"'";
		}
			sql+=" order by t1.i_order,t.i_order";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);		
	}
	/**
	 * 将一行数据转换成多行，RowBase包含四个字段，ID(字段名)、NAME(字段注释)、VALUE(值0、1、2)、DETAIL(详情),COUNT(占用单元格数)
	 * 
	 * @param rowBase
	 * @return
	 * @throws Exception 
	 */
	public List<RowBase> getParseData(RowBase rowBase,Map<String, String> params) throws Exception {
		String tableName="";
		if(params.get("checktype").equals("1")){
			tableName="QC_MASSCONTROLCHECK";
		}else if(params.get("checktype").equals("2")){
			tableName="QC_SAMPLINGLOCALECHECK";
		}else if(params.get("checktype").equals("3")){
			tableName="QC_SAMPLINGLOCALEFLUEGAS";
		}else if(params.get("checktype").equals("4")){
			tableName="QC_OPERATECHECKINFO";
		}
		List<RowBase> resultList = new ArrayList<RowBase>();
		List<RowBase> tableFieldList = getTableFieldList(tableName);	
		for (RowBase bean : tableFieldList) {
			RowBase rowBaseBean = new RowBase();
			String fieldvalue=rowBase.getSafeString(bean.getSafeString("id"));//取到字段值
			String content=bean.getSafeString("name");//检查内容
			String value="";
			if(fieldvalue.length()>0){
				value = fieldvalue.substring(0, 1);
				if(value.equals("0")){
					value="否";
				}else if(value.equals("1")){
					value="是";
				}else{
					value="部分存在问题";
				}
			}	
			String explain="";
			if (fieldvalue.length() >= 3) {
				explain = fieldvalue.substring(2, fieldvalue.length());
			}
			rowBaseBean.put("VALUE", value);//值
			rowBaseBean.put("CONTENT", content);//检查内容
			rowBaseBean.put("EXPLAIN", explain);//检查内容说明
			resultList.add(rowBaseBean);				
		}		
		return resultList;
	}
	/**
	 * 获取表结构信息,RowBase包含三个字段 name(字段注释)、id(字段名)、type(字段类型)
	 * 
	 * @param tableName
	 * @return
	 */
	public List<RowBase> getTableFieldList(String tableName)throws Exception{
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select t1.COLUMN_NAME as id,t2.COMMENTS as name from user_tab_columns t1"
				+" LEFT JOIN user_col_comments t2 on t2.table_name = t1.TABLE_NAME"
				+" and t2.column_name = t1.COLUMN_NAME  where t1.Table_Name ='"+tableName+"'"
				+" AND t1.COLUMN_NAME not in ('GUID', 'CHECKID')";
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			return rows;
	}
}
